CREATE TABLE users.accounts (
    "id" BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    username TEXT UNIQUE NOT NULL CHECK (length(username) > 3),
    password TEXT NOT NULL,
    created TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT TIMEZONE('UTC', now())
);

CREATE TABLE users.confirmations (
    client BIGINT PRIMARY KEY,
    confirmation SMALLINT NOT NULL DEFAULT 0,
    email TEXT UNIQUE,
    emails TEXT[],
    phone TEXT UNIQUE,
    FOREIGN KEY (client) REFERENCES users.accounts ("id")
);

CREATE TABLE users.changes (
    client BIGINT PRIMARY KEY,
    username TIMESTAMP WITHOUT TIME ZONE,
    password TIMESTAMP WITHOUT TIME ZONE,
    email TIMESTAMP WITHOUT TIME ZONE,
    phone TIMESTAMP WITHOUT TIME ZONE,
    FOREIGN KEY (client) REFERENCES users.accounts ("id")
);

CREATE TABLE users.aliases (
    "type" TEXT NOT NULL,
    target BIGINT NOT NULL,
    "id" UUID PRIMARY KEY DEFAULT public.uuid_generate_v4()
)

CREATE TABLE users.bots (
    client BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    owner BIGINT NOT NULL,
    FOREIGN KEY (client) REFERENCES users.accounts ("id"),
    FOREIGN KEY (owner) REFERENCES users.accounts ("id")
);
